package com.china08.yxyapi.service.lesson.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.china08.yxyapi.service.lesson.TecCloudLessonService;

@Service
public class TecCloudLessonServiceImpl implements TecCloudLessonService {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Override
    public Map<String, Object> listForTec(String schoolId, String authorId, String catalog1, String catalog2, String catalog3, String catalog4, String catalog5, String lessonStatus, Integer isJingpinLesson, String keyword, Integer pageNum) {

        System.out.println(lessonStatus);

        List<Object> paramList = new ArrayList<Object>();
        StringBuffer sb1 = new StringBuffer();
        sb1.append("   select                                               ");
        sb1.append("     count(id)                                          ");
        sb1.append("   from yxy_lessons.yxy_lessons_cloud_lesson            ");
        sb1.append("   where school_id = ?                                  ");
        sb1.append("        and status != -1                                ");
        sb1.append("        and author_id = ?                               ");
        paramList.add(schoolId);
        paramList.add(authorId);
        if (isJingpinLesson == 0 || isJingpinLesson == 1) {
            sb1.append("    and is_jingpin_lesson = ?                       ");
            paramList.add(isJingpinLesson);
        }

        if (StringUtils.isNotBlank(lessonStatus)) {
            if ("0".equals(lessonStatus)) {
                sb1.append(" and status = 0                                 ");
            }
            if ("1".equals(lessonStatus)) {
                sb1.append(" and status = 1                                 ");
            }
            if ("3".equals(lessonStatus)) {
                sb1.append(" and status = 3                                 ");
            }
            if ("11".equals(lessonStatus)) {
                sb1.append(" and status = 2 and is_visible  = 1             ");
            }
            if ("12".equals(lessonStatus)) {
                sb1.append(" and status = 2 and is_visible  = 0             ");
            }
        }

        if (StringUtils.isNotBlank(catalog1)) {
            sb1.append("  and catalog_id1 = ?                               ");
            paramList.add(catalog1);
        }
        if (StringUtils.isNotBlank(catalog2)) {
            sb1.append("  and catalog_id2 = ?                               ");
            paramList.add(catalog2);
        }
        if (StringUtils.isNotBlank(catalog3)) {
            sb1.append("  and catalog_id3 = ?                               ");
            paramList.add(catalog3);
        }
        if (StringUtils.isNotBlank(catalog4)) {
            sb1.append("  and catalog_id4 = ?                               ");
            paramList.add(catalog4);
        }
        if (StringUtils.isNotBlank(catalog5)) {
            sb1.append("  and catalog_id5 = ?                               ");
            paramList.add(catalog5);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append("  and name like ?                                   ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select                                                ");
        sb2.append("    name,                                               ");
        sb2.append("    id,                                                 ");
        sb2.append("    cover,                                              ");
        sb2.append("    catalog_id1,                                        ");
        sb2.append("    catalog_id2,                                        ");
        sb2.append("    catalog_id3,                                        ");
        sb2.append("    catalog_id4,                                        ");
        sb2.append("    catalog_id5,                                        ");
        sb2.append("    author,                                             ");
        sb2.append("    status,                                             ");
        sb2.append("    is_visible,                                         ");
        sb2.append("    count,                                              ");
        sb2.append("    duration,                                           ");
        sb2.append("    is_jingpin_lesson,                                  ");
        sb2.append("    up_Date,                                            ");
        sb2.append("    create_date                                         ");
        sb2.append("   from yxy_lessons.yxy_lessons_cloud_lesson            ");
        sb2.append("   where school_id = ?                                  ");
        sb2.append("        and status != -1                                ");
        sb2.append("        and author_id = ?                               ");
        if (isJingpinLesson == 0 || isJingpinLesson == 1) {
            sb2.append("    and is_jingpin_lesson = ?                       ");
        }

        if (StringUtils.isNotBlank(lessonStatus)) {
            if ("0".equals(lessonStatus)) {
                sb2.append(" and status = 0                                 ");
            }
            if ("1".equals(lessonStatus)) {
                sb2.append(" and status = 1                                 ");
            }
            if ("3".equals(lessonStatus)) {
                sb2.append(" and status = 3                                 ");
            }
            if ("11".equals(lessonStatus)) {
                sb2.append(" and status = 2 and is_visible  = 1             ");
            }
            if ("12".equals(lessonStatus)) {
                sb2.append(" and status = 2 and is_visible  = 0             ");
            }
        }
        if (StringUtils.isNotBlank(catalog1)) {
            sb2.append("  and catalog_id1 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog2)) {
            sb2.append("  and catalog_id2 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog3)) {
            sb2.append("  and catalog_id3 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog4)) {
            sb2.append("  and catalog_id4 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog5)) {
            sb2.append("  and catalog_id5 = ?                               ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append("  and name like ?                                   ");
        }
        sb2.append(" order by create_date desc, id asc                      ");
        sb2.append(" limit ?,?                                              ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Map<String, Object> listForEdu(String catalog1, String catalog2, String catalog3, String catalog4, String catalog5, String status, Integer isJingPin, String keyword, Integer pageNum) {
        List<Object> paramList = new ArrayList<Object>();

        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                                     ");
        sb1.append("   count(distinct t1.id )                                   ");
        sb1.append(" from yxy_lessons.yxy_lessons_cloud_lesson t1               ");
        sb1.append("   left join yxy_lessons.yxy_lessons_cloud_lesson_item t2   ");
        sb1.append("     on (t1.id = t2.lesson_id)                              ");
        sb1.append(" where t1.status != -1                                      ");
        if (isJingPin == 0 || isJingPin == 1) {
            sb1.append(" and (t1.is_jingpin_lesson = ?)");
            paramList.add(isJingPin);
        }
        // 待审核：获取有课时未待审核的课程
        if ("0".equals(status)) {
            sb1.append(" and (t1.status = 1 or t2.status = 1)                   ");
        }
        // 已上架：课程通过，并且已经上架
        if ("11".equals(status)) {
            sb1.append(" and t1.status = 2                                      ");
            sb1.append(" and t1.is_visible = 1                                  ");
        }
        // 已下架：课程通过，并且已经下架
        if ("12".equals(status)) {
            sb1.append(" and t1.status = 2                                      ");
            sb1.append(" and t1.is_visible = 0                                  ");
        }
        // 未通过：课程通过但课时有未通过和课程就没有通过
        if ("3".equals(status)) {
            sb1.append(" and (t1.status = 3 or t2.status = 3)                   ");
        }
        if (StringUtils.isNotBlank(catalog1)) {
            sb1.append("  and catalog_id1 = ?                               ");
            paramList.add(catalog1);
        }
        if (StringUtils.isNotBlank(catalog2)) {
            sb1.append("  and catalog_id2 = ?                               ");
            paramList.add(catalog2);
        }
        if (StringUtils.isNotBlank(catalog3)) {
            sb1.append("  and catalog_id3 = ?                               ");
            paramList.add(catalog3);
        }
        if (StringUtils.isNotBlank(catalog4)) {
            sb1.append("  and catalog_id4 = ?                               ");
            paramList.add(catalog4);
        }
        if (StringUtils.isNotBlank(catalog5)) {
            sb1.append("  and catalog_id5 = ?                               ");
            paramList.add(catalog5);
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb1.append("  and name like ?                                   ");
            paramList.add(keyword);
        }
        Integer allPage = jdbcTemplate.queryForObject(sb1.toString(), paramList.toArray(), Integer.class);

        StringBuffer sb2 = new StringBuffer();
        sb2.append("  select distinct                                       ");
        sb2.append("    t1.id,                                              ");
        sb2.append("    t1.name,                                            ");
        sb2.append("    t1.cover,                                           ");
        sb2.append("    t1.author,                                          ");
        sb2.append("    t1.status,                                          ");
        sb2.append("    t1.count,                                           ");
        sb2.append("    t1.duration,                                        ");
        sb2.append("    t1.school_nick,                                     ");
        sb2.append("    t1.vv,                                              ");
        sb2.append("    t1.up_date,                                         ");
        sb2.append("    t1.down_date,                                       ");
        sb2.append("    t1.create_date,                                     ");
        sb2.append("    t1.update_date,                                     ");
        sb2.append("    t1.audit_date,                                     ");
        sb2.append("    t1.catalog_id1,                                     ");
        sb2.append("    t1.catalog_id2,                                     ");
        sb2.append("    t1.catalog_id3,                                     ");
        sb2.append("    t1.catalog_id4,                                     ");
        sb2.append("    t1.catalog_id5,                                     ");
        sb2.append("    t1.is_visible,                                      ");
        sb2.append("    t1.is_jingpin_lesson                                ");
        sb2.append("  from yxy_lessons.yxy_lessons_cloud_lesson t1          ");
        sb2.append("    left join yxy_lessons.yxy_lessons_cloud_lesson_item t2    ");
        sb2.append("      on (t1.id = t2.lesson_id)                         ");
        sb2.append("  where t1.status != -1                                ");
        if (isJingPin == 0 || isJingPin == 1) {
            sb2.append(" and (t1.is_jingpin_lesson = ?)                     ");
        }
        // 待审核：获取有课时未待审核的课程
        if ("0".equals(status)) {
            sb2.append(" and (t1.status = 1 or t2.status = 1)                                      ");
        }
        // 已上架：课程通过，并且已经上架
        if ("11".equals(status)) {
            sb2.append(" and t1.status = 2                                      ");
            sb2.append(" and t1.is_visible = 1                                  ");
        }
        // 已下架：课程通过，并且已经下架
        if ("12".equals(status)) {
            sb2.append(" and t1.status = 2                                      ");
            sb2.append(" and t1.is_visible = 0                                  ");
        }
        // 未通过：课程通过但课时有未通过和课程就没有通过
        if ("3".equals(status)) {
            sb2.append(" and (t1.status = 3 or t2.status = 3)                   ");
        }
        if (StringUtils.isNotBlank(catalog1)) {
            sb2.append("  and catalog_id1 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog2)) {
            sb2.append("  and catalog_id2 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog3)) {
            sb2.append("  and catalog_id3 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog4)) {
            sb2.append("  and catalog_id4 = ?                               ");
        }
        if (StringUtils.isNotBlank(catalog5)) {
            sb2.append("  and catalog_id5 = ?                               ");
        }
        if (StringUtils.isNotBlank(keyword)) {
            sb2.append("  and name like ?                                   ");
        }
        sb2.append(" order by update_date desc, id asc                      ");
        sb2.append(" limit ?,?                                              ");
        paramList.add((pageNum - 1) * 10);
        paramList.add(10);
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sb2.toString(), paramList.toArray());

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("allPage", allPage);
        map.put("list", list);
        return map;
    }

    @Override
    public Integer lessonItemStatusCount() {
        StringBuffer sb1 = new StringBuffer();
        sb1.append(" select                                                     ");
        sb1.append("   count(distinct t1.id )                                   ");
        sb1.append(" from yxy_lessons.yxy_lessons_cloud_lesson t1               ");
        sb1.append("   left join yxy_lessons.yxy_lessons_cloud_lesson_item t2   ");
        sb1.append("     on (t1.id = t2.lesson_id)                              ");
        sb1.append(" where t1.status != -1 and (t1.status = 1 or t2.status = 1) ");
        return jdbcTemplate.queryForObject(sb1.toString(), Integer.class);
    }
}
